#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
百度地图坐标转换为腾讯地图坐标的迁移脚本（本地算法版本）
将数据库中的BD-09坐标转换为GCJ-02坐标

使用本地坐标转换算法，不依赖外部API
"""

import pymysql
import math
import logging
from datetime import datetime
from typing import List, Dict, Tuple

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('coordinate_migration.log'),
        logging.StreamHandler()
    ]
)
logger = logging.getLogger(__name__)

# 数据库配置
DB_CONFIG = {
    'host': '1.92.133.195',
    'port': 3306,
    'user': 'weixin',
    'password': 'k5Ykx2WSmjHJaPFs',
    'database': 'weixin',
    'charset': 'utf8mb4'
}

# 需要转换的表和字段配置
TABLES_TO_CONVERT = [
    {
        'table': 'venues',
        'id_field': 'id',
        'lat_field': 'latitude',
        'lng_field': 'longitude'
    },
    {
        'table': 'activities', 
        'id_field': 'id',
        'lat_field': 'latitude',
        'lng_field': 'longitude'
    }
]

class CoordinateConverter:
    """坐标转换器 - 使用本地算法"""
    
    def __init__(self):
        self.connection = None
        self.total_converted = 0
        
    def connect_database(self) -> bool:
        """连接数据库"""
        try:
            self.connection = pymysql.connect(**DB_CONFIG)
            logger.info("数据库连接成功")
            return True
        except Exception as e:
            logger.error(f"数据库连接失败: {e}")
            return False
    
    def close_database(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()
            logger.info("数据库连接已关闭")
    
    def bd09_to_gcj02(self, bd_lng: float, bd_lat: float) -> Tuple[float, float]:
        """
        BD-09坐标转换为GCJ-02坐标
        
        Args:
            bd_lng: 百度坐标系经度
            bd_lat: 百度坐标系纬度
            
        Returns:
            Tuple[float, float]: (GCJ-02经度, GCJ-02纬度)
        """
        x = bd_lng - 0.0065
        y = bd_lat - 0.006
        z = math.sqrt(x * x + y * y) - 0.00002 * math.sin(y * math.pi)
        theta = math.atan2(y, x) - 0.000003 * math.cos(x * math.pi)
        gcj_lng = z * math.cos(theta)
        gcj_lat = z * math.sin(theta)
        return gcj_lng, gcj_lat
    
    def backup_table(self, table_name: str) -> bool:
        """备份表数据"""
        try:
            cursor = self.connection.cursor()
            backup_table_name = f"{table_name}_backup_{datetime.now().strftime('%Y%m%d_%H%M%S')}"
            
            # 创建备份表
            cursor.execute(f"CREATE TABLE {backup_table_name} AS SELECT * FROM {table_name}")
            self.connection.commit()
            
            logger.info(f"表 {table_name} 备份完成，备份表名: {backup_table_name}")
            return True
        except Exception as e:
            logger.error(f"备份表 {table_name} 失败: {e}")
            return False
        finally:
            cursor.close()
    
    def get_coordinates_from_table(self, table_config: Dict) -> List[Dict]:
        """从表中获取坐标数据"""
        try:
            cursor = self.connection.cursor()
            query = f"""
                SELECT {table_config['id_field']}, 
                       {table_config['lat_field']}, 
                       {table_config['lng_field']}
                FROM {table_config['table']} 
                WHERE {table_config['lat_field']} IS NOT NULL 
                  AND {table_config['lng_field']} IS NOT NULL
                  AND {table_config['lat_field']} != 0 
                  AND {table_config['lng_field']} != 0
            """
            
            cursor.execute(query)
            results = cursor.fetchall()
            
            coordinates = []
            for row in results:
                coordinates.append({
                    'id': row[0],
                    'latitude': float(row[1]),
                    'longitude': float(row[2])
                })
            
            logger.info(f"从表 {table_config['table']} 获取到 {len(coordinates)} 条坐标记录")
            return coordinates
            
        except Exception as e:
            logger.error(f"获取表 {table_config['table']} 坐标数据失败: {e}")
            return []
        finally:
            cursor.close()
    
    def convert_coordinates_local(self, coordinates: List[Dict]) -> List[Dict]:
        """
        使用本地算法批量转换坐标
        """
        converted_results = []
        
        for coord in coordinates:
            try:
                # 转换坐标
                gcj_lng, gcj_lat = self.bd09_to_gcj02(coord['longitude'], coord['latitude'])
                
                converted_results.append({
                    'id': coord['id'],
                    'original_lat': coord['latitude'],
                    'original_lng': coord['longitude'],
                    'converted_lat': gcj_lat,
                    'converted_lng': gcj_lng
                })
                
            except Exception as e:
                logger.error(f"转换坐标失败 ID {coord['id']}: {e}")
                continue
        
        logger.info(f"本地算法转换完成，成功转换 {len(converted_results)} 个坐标")
        return converted_results
    
    def update_coordinates_in_table(self, table_config: Dict, converted_coordinates: List[Dict]) -> bool:
        """更新表中的坐标数据"""
        if not converted_coordinates:
            return True
        
        try:
            cursor = self.connection.cursor()
            
            # 批量更新坐标
            update_query = f"""
                UPDATE {table_config['table']} 
                SET {table_config['lat_field']} = %s, 
                    {table_config['lng_field']} = %s
                WHERE {table_config['id_field']} = %s
            """
            
            update_data = []
            for coord in converted_coordinates:
                update_data.append((
                    coord['converted_lat'],
                    coord['converted_lng'], 
                    coord['id']
                ))
            
            cursor.executemany(update_query, update_data)
            self.connection.commit()
            
            logger.info(f"表 {table_config['table']} 坐标更新完成，更新了 {len(converted_coordinates)} 条记录")
            self.total_converted += len(converted_coordinates)
            return True
            
        except Exception as e:
            logger.error(f"更新表 {table_config['table']} 坐标失败: {e}")
            self.connection.rollback()
            return False
        finally:
            cursor.close()
    
    def verify_conversion(self, table_config: Dict, sample_size: int = 5) -> bool:
        """验证转换结果"""
        try:
            cursor = self.connection.cursor()
            
            # 随机抽取几条记录验证
            query = f"""
                SELECT {table_config['id_field']}, 
                       {table_config['lat_field']}, 
                       {table_config['lng_field']}
                FROM {table_config['table']} 
                WHERE {table_config['lat_field']} IS NOT NULL 
                  AND {table_config['lng_field']} IS NOT NULL
                ORDER BY RAND() 
                LIMIT {sample_size}
            """
            
            cursor.execute(query)
            results = cursor.fetchall()
            
            logger.info(f"表 {table_config['table']} 验证样本:")
            for row in results:
                logger.info(f"  ID: {row[0]}, 纬度: {row[1]}, 经度: {row[2]}")
            
            return True
            
        except Exception as e:
            logger.error(f"验证表 {table_config['table']} 失败: {e}")
            return False
        finally:
            cursor.close()
    
    def migrate_coordinates(self) -> bool:
        """执行坐标迁移"""
        logger.info("开始坐标迁移任务（使用本地算法）")
        
        # 连接数据库
        if not self.connect_database():
            return False
        
        try:
            for table_config in TABLES_TO_CONVERT:
                table_name = table_config['table']
                logger.info(f"开始处理表: {table_name}")
                
                # 1. 备份表
                if not self.backup_table(table_name):
                    logger.error(f"表 {table_name} 备份失败，跳过此表")
                    continue
                
                # 2. 获取坐标数据
                coordinates = self.get_coordinates_from_table(table_config)
                if not coordinates:
                    logger.warning(f"表 {table_name} 没有需要转换的坐标数据")
                    continue
                
                # 3. 转换坐标（本地算法）
                converted_coordinates = self.convert_coordinates_local(coordinates)
                
                # 4. 更新数据库
                if converted_coordinates:
                    self.update_coordinates_in_table(table_config, converted_coordinates)
                
                # 5. 验证结果
                self.verify_conversion(table_config)
                
                logger.info(f"表 {table_name} 处理完成")
            
            # 输出总结
            logger.info(f"坐标迁移任务完成!")
            logger.info(f"总计转换坐标: {self.total_converted} 个")
            
            return True
            
        except Exception as e:
            logger.error(f"坐标迁移过程出错: {e}")
            return False
        finally:
            self.close_database()
    
    def test_conversion_sample(self):
        """测试转换算法"""
        logger.info("测试坐标转换算法")
        
        # 使用实际数据库中的坐标进行测试
        test_coordinates = [
            {'id': 12, 'lat': 39.8121, 'lng': 116.573, 'name': '越海全球物流篮球场'},
            {'id': 13, 'lat': 39.8215, 'lng': 116.564, 'name': '智慧电竞中心2号足球场'},
            {'id': 14, 'lat': 39.8209, 'lng': 116.566, 'name': '智慧电竞中心1号足球场'}
        ]
        
        logger.info("转换结果对比:")
        for coord in test_coordinates:
            gcj_lng, gcj_lat = self.bd09_to_gcj02(coord['lng'], coord['lat'])
            
            lat_diff = abs(gcj_lat - coord['lat'])
            lng_diff = abs(gcj_lng - coord['lng'])
            
            logger.info(f"  {coord['name']}:")
            logger.info(f"    原始坐标 (BD-09): 纬度 {coord['lat']}, 经度 {coord['lng']}")
            logger.info(f"    转换坐标 (GCJ-02): 纬度 {gcj_lat:.6f}, 经度 {gcj_lng:.6f}")
            logger.info(f"    坐标偏移: 纬度差 {lat_diff:.6f}, 经度差 {lng_diff:.6f}")


def main():
    """主函数"""
    print("百度地图坐标转换为腾讯地图坐标迁移脚本（本地算法版本）")
    print("=" * 60)
    
    converter = CoordinateConverter()
    
    # 先测试转换算法
    print("\n🧪 测试转换算法...")
    converter.test_conversion_sample()
    
    print("\n" + "="*60)
    
    # 确认执行
    confirm = input("\n此操作将修改数据库中的坐标数据，确定要继续吗？(y/N): ")
    if confirm.lower() != 'y':
        print("迁移已取消")
        return
    
    # 执行迁移
    success = converter.migrate_coordinates()
    
    if success:
        print("\n✅ 迁移完成！请检查日志文件获取详细信息。")
    else:
        print("\n❌ 迁移过程中出现错误，请检查日志文件。")


if __name__ == "__main__":
    main() 